package com.yellowpage.bigdata.service;

import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.bigdata.data.NspOnofflogImeiDayregionSum;
import com.yellowpage.bigdata.dao.NspOnofflogImeiDayregionSumDao;

public class NspOnofflogImeiDayregionSumServiceImpl extends BaseServiceImpl implements NspOnofflogImeiDayregionSumService {
	  private NspOnofflogImeiDayregionSumDao nspOnofflogImeiDayregionSumDao;
	
	  public void setNspOnofflogImeiDayregionSumDao(NspOnofflogImeiDayregionSumDao nspOnofflogImeiDayregionSumDao) {
		  this.nspOnofflogImeiDayregionSumDao = nspOnofflogImeiDayregionSumDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public NspOnofflogImeiDayregionSum getNspOnofflogImeiDayregionSum(Long id){
	      return nspOnofflogImeiDayregionSumDao.get(id);
	  }
	  
    public List<NspOnofflogImeiDayregionSum> getAllNspOnofflogImeiDayregionSum(){
        return nspOnofflogImeiDayregionSumDao.getAll();
    }
    
    public List<Map> queryNspOnofflogImeiDayregionSumByPage(Map queryMap, PageRender pageRender) {		
    	String t2 = queryMap.get("regionCode").equals("") ? queryMap.get("regionCode1").toString() : " and REGION_CODE in "+queryMap.get("regionCode");
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"SELECT a.NOTE , a.CREATE_TIME , a.ONOFFLOG_IMEI_DAYREGION_SUM_ID , a.IMEI_DAY_COUNT , a.IMEI_ALL_COUNT , a.IMEI_NEW_COUNT , a.LOG_DATE , a.REGION_CODE , " +
				"a.OPT_TYPE , a.CREATE_USER,a.NEXT_DAY_RETAINED as NEXT_DAY_RETAINED,a.THREE_DAY_RETAINED as THREE_DAY_RETAINED,a.SEVEN_DAY_RETAINED as SEVEN_DAY_RETAINED,a.THIRTY_DAY_RETAINED as THIRTY_DAY_RETAINED," +
				"(SELECT b.NAME FROM OMC_REGION b WHERE b.REGION_CODE = a.REGION_CODE) as REGION_NAME  " +
				"FROM NSP_ONOFFLOG_IMEI_DAYREGION_SUM a WHERE 1=1 " + t2)
							    .addCondition(" and a.LOG_DATE >= :logDate ")
				    			.addCondition(" and a.LOG_DATE <= :query_logDate_end ")
//							    .addCondition(" and REGION_CODE like :regionCode ")
							    .addCondition(" and a.OPT_TYPE = :query_optType ")
							    ;
		List<Map> resultList;
		//��pageRender���ڵ�ʱ����ת
		if (pageRender != null)
		{ resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);}

		//����ȡȫ��
		else

		{ resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);}


		return resultList;
	}
    
    public List<Map> queryregion(Map queryMap) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT c.REGION_CODE FROM OMC_REGION c WHERE 1=1")
				.addCondition(" and c.NAME like :regionCode ");

		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
    
    public List<Map> queryNspOnofflogImeiDayregionByPage(Map queryMap,
			PageRender pageRender) {
    	String t2 = queryMap.get("regionCode").equals("") ? queryMap.get("regionCode1").toString() : " and REGION_CODE in "+queryMap.get("regionCode");
    	String t3 = queryMap.get("logDate").equals("") ? "" : " and b.LOG_DATE >= "+queryMap.get("logDate");
    	String t4 = queryMap.get("query_logDate_end").equals("") ? "" : " and b.LOG_DATE <= "+queryMap.get("query_logDate_end");
    	QueryAssistantSQL sqlAssistant = baseDao
    			.newQueryAssistantSQL(
    					"select b.LOG_DATE,b.REGION_CODE,"
    							+ "sum(CASE WHEN OPT_TYPE = '3' THEN IMEI_DAY_COUNT ELSE 0 END ) as LXNUM,"
    							+ "sum( CASE WHEN OPT_TYPE = 'Z' THEN IMEI_DAY_COUNT ELSE 0 END ) as ZDNUM,"
    							+ "sum( CASE WHEN OPT_TYPE = 'A' THEN IMEI_ALL_COUNT ELSE 0 END ) as ALLNUM,"
    							+ "sum( CASE WHEN OPT_TYPE = 'A' THEN IMEI_NEW_COUNT ELSE 0 END ) as NEWNUM,"
    							+ "(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = b.REGION_CODE) as REGION_NAME, "
    							+ "sum( CASE WHEN OPT_TYPE = 'A' THEN IMEI_DAY_COUNT ELSE 0 END ) as JHNUM "
    							+ "FROM NSP_ONOFFLOG_IMEI_DAYREGION_SUM b WHERE 1=1 " + t2 + t3 + t4 + " AND (b.OPT_TYPE ='3' OR b.OPT_TYPE ='Z' OR b.OPT_TYPE ='A') ");
    	                  sqlAssistant.setTail(" group by b.LOG_DATE,b.REGION_CODE order by b.REGION_CODE desc");
//		.addCondition(" and b.LOG_DATE >= :logDate ")
//		.addCondition(" and b.LOG_DATE <= :query_logDate_end ");
//		.addCondition(" and REGION_CODE like :regionCode ");
sqlAssistant.setTail(" group by b.LOG_DATE,b.REGION_CODE order by b.REGION_CODE desc");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
    
    public List<Map> queryNspOnofflogImeiDayregionByPage(Map queryMap) {

    	String t2 = queryMap.get("regionCode").equals("") ? queryMap.get("regionCode1").toString() : " and REGION_CODE in "+queryMap.get("regionCode");
    	String t3 = queryMap.get("logDate").equals("") ? "" : " and b.LOG_DATE >= "+queryMap.get("logDate");
    	String t4 = queryMap.get("query_logDate_end").equals("") ? "" : " and b.LOG_DATE <= "+queryMap.get("query_logDate_end");
    	QueryAssistantSQL sqlAssistant = baseDao
		.newQueryAssistantSQL(
				"select b.LOG_DATE,b.REGION_CODE,"
						+ "sum(CASE WHEN OPT_TYPE = '3' THEN IMEI_DAY_COUNT ELSE 0 END ) as LXNUM,"
						+ "sum( CASE WHEN OPT_TYPE = 'Z' THEN IMEI_DAY_COUNT ELSE 0 END ) as ZDNUM,"
						+ "sum( CASE WHEN OPT_TYPE = 'A' THEN IMEI_ALL_COUNT ELSE 0 END ) as ALLNUM,"
						+ "sum( CASE WHEN OPT_TYPE = 'A' THEN IMEI_NEW_COUNT ELSE 0 END ) as NEWNUM,"
						+ "(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = b.REGION_CODE) as REGION_NAME, "
						+ "sum( CASE WHEN OPT_TYPE = 'A' THEN IMEI_DAY_COUNT ELSE 0 END ) as JHNUM "
						+ "FROM NSP_ONOFFLOG_IMEI_DAYREGION_SUM b WHERE 1=1 " + t2 + t3 + t4 + " AND (b.OPT_TYPE ='3' OR b.OPT_TYPE ='Z' OR b.OPT_TYPE ='A') ");
                  sqlAssistant.setTail(" group by b.LOG_DATE,b.REGION_CODE order by b.REGION_CODE desc");
		
//    	QueryAssistantSQL sqlAssistant = baseDao
//		.newQueryAssistantSQL(
//				"select DISTINCT b.LOG_DATE,b.REGION_CODE,"
//						+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.OPT_TYPE='3' and c.REGION_CODE=b.REGION_CODE) as LXNUM,"
//						+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.OPT_TYPE='Z' and c.REGION_CODE=b.REGION_CODE) as ZDNUM,"
//						+ "(select c.IMEI_ALL_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.REGION_CODE=b.REGION_CODE AND c.OPT_TYPE='A') as ALLNUM,"
//						+ "(select c.IMEI_NEW_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.REGION_CODE=b.REGION_CODE AND c.OPT_TYPE='A') as NEWNUM,"
//						+ "(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = b.REGION_CODE) as REGION_NAME, "
//						+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE AND c.OPT_TYPE='A' and c.REGION_CODE=b.REGION_CODE) as JHNUM "
//						+ "FROM NSP_ONOFFLOG_IMEI_DAYREGION_SUM b WHERE b.REGION_CODE<>'A' "+t2)
//		.addCondition(" and b.LOG_DATE >= :logDate ")
//		.addCondition(" and b.LOG_DATE <= :query_logDate_end ");
////		.addCondition(" and REGION_CODE like :regionCode ");
//    	sqlAssistant.setTail(" group by b.LOG_DATE,b.REGION_CODE order by b.REGION_CODE desc");

		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
    
    public List<Map> queryNspOnofflogImeiDayregionByPageAll(Map queryMap,
			PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select DISTINCT b.LOG_DATE,b.REGION_CODE,"
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.OPT_TYPE='3' and c.REGION_CODE=b.REGION_CODE) as LXNUM,"
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.OPT_TYPE='Z' and c.REGION_CODE=b.REGION_CODE) as ZDNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION='A' and c.CHANNEL_NO='android' and c.OPERATOR='A') as ANDROIDNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION='A' and c.CHANNEL_NO='101' and c.OPERATOR='A') as IOSNUM,"
								+ "(select c.IMEI_ALL_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.REGION_CODE=b.REGION_CODE AND c.OPT_TYPE='A') as ALLNUM,"
								+ "(select c.IMEI_NEW_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.REGION_CODE=b.REGION_CODE AND c.OPT_TYPE='A') as NEWNUM,"
								+ "(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = b.REGION_CODE) as REGION_NAME, "
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE AND c.OPT_TYPE='A' and c.REGION_CODE=b.REGION_CODE) as JHNUM "
								+ "FROM NSP_ONOFFLOG_IMEI_DAYREGION_SUM b WHERE 1=1 ")
				.addCondition(" and b.LOG_DATE >= :logDate ")
				.addCondition(" and b.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and REGION_CODE like :regionCode ");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);
		return resultList;
	}
	
	public List<Map> queryNspOnofflogImeiDayregionByPageAll(Map queryMap) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select DISTINCT b.LOG_DATE,b.REGION_CODE,"
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.OPT_TYPE='3' and c.REGION_CODE=b.REGION_CODE) as LXNUM,"
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.OPT_TYPE='Z' and c.REGION_CODE=b.REGION_CODE) as ZDNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION='A' and c.CHANNEL_NO='android' and c.OPERATOR='A') as ANDROIDNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION='A' and c.CHANNEL_NO='101' and c.OPERATOR='A') as IOSNUM,"
								+ "(select c.IMEI_ALL_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.REGION_CODE=b.REGION_CODE AND c.OPT_TYPE='A') as ALLNUM,"
								+ "(select c.IMEI_NEW_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE and c.REGION_CODE=b.REGION_CODE AND c.OPT_TYPE='A') as NEWNUM,"
								+ "(SELECT c.NAME FROM OMC_REGION c WHERE c.REGION_CODE = b.REGION_CODE) as REGION_NAME, "
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAYREGION_SUM c where c.LOG_DATE=b.LOG_DATE AND c.OPT_TYPE='A' and c.REGION_CODE=b.REGION_CODE) as JHNUM "
								+ "FROM NSP_ONOFFLOG_IMEI_DAYREGION_SUM b WHERE 1=1 ")
				.addCondition(" and b.LOG_DATE >= :logDate ")
				.addCondition(" and b.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and REGION_CODE like :regionCode ");

		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
    
    public void saveNspOnofflogImeiDayregionSum(NspOnofflogImeiDayregionSum nspOnofflogImeiDayregionSum){
        nspOnofflogImeiDayregionSumDao.save(nspOnofflogImeiDayregionSum);
    }
    
	  public void updateNspOnofflogImeiDayregionSum(NspOnofflogImeiDayregionSum nspOnofflogImeiDayregionSum){
	      nspOnofflogImeiDayregionSumDao.update(nspOnofflogImeiDayregionSum);
	  }
	  
	  public void delete(List deleteList){
	      nspOnofflogImeiDayregionSumDao.remove(deleteList);
	  }
	  
	  
	 
	
}	